<%@ page contentType="text/html; charset=UTF-8" language="java" %>
<%@ page import="com.base.myproject.server.tools.*" %>
<%@ page import="com.base.myproject.client.jdbc.DataSet,java.util.Date" %>
<%@page import="java.text.SimpleDateFormat,java.util.Calendar,java.math.BigDecimal"%>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">

<html>
<head>
<%
	String excel = request.getParameter("excel");
	if (excel != null && !excel.equals("")) {
		response.setHeader("Content-disposition","attachment; filename=hostelDetail.xls");
	}
	String dep = request.getParameter("dep");
	String no = request.getParameter("no");
	String liveBegin = request.getParameter("liveBegin");
	String liveEnd = request.getParameter("liveEnd");
	String leaveBegin = request.getParameter("leaveBegin");
	String leaveEnd = request.getParameter("leaveEnd");
	
	DataSetTool DST = new DataSetTool();
	String sql = "select c.code as code,bednum,a.bname as name,employnum,(select name from systeminfo where id = up_department) as up_department,(select name from systeminfo where id = systemid) as systemid,sex,convert(varchar(10),livedate,120) as livedate,nativeplace,cerno,convert(varchar(10),leavedate,120) as leavedate,convert(varchar(10),entrydate,120) as entrydate,convert(varchar(10),Resignationdate,120) as Resignationdate from " +
				 "(select bcode,bname,Identifier + bcode as employnum,up_department,systemid,sex,nativeplace from bcode) as a inner join " +
				 "(select bcode,bednum,livedate,leavedate,code from hr_hostel_bcode) as c on a.bcode=c.bcode left join " +
				 "(select bcode,cerno from bcode_cer where cerSpecies='暂住证') as b on a.bcode=b.bcode left join " +
				 "(select entrydate, Resignationdate, bcode from bcode_job)as d on a.bcode=d.bcode where 1=1";
	if (!dep.equals("0")) {
		sql += " and systemid like '" + dep + "%'";
	}
	
	if (!no.equals("0")) {
		sql += " and employnum like '%" + no + "%'";
	}
	
	if (!liveBegin.equals("0") && !liveEnd.equals("0")) {
		sql += " and livedate between '"+liveBegin+"' and '"+liveEnd+"'";
	}
	
	if (!leaveBegin.equals("0") && !leaveEnd.equals("0")) {
		sql += " and leavedate between '"+leaveBegin+"' and '"+leaveEnd+"'";
	}
		
	DataSet totelList = DST.getDataSet(sql);
%>
</head>
<body>
	<table style="border-collapse: collapse" border="1" align="center"  cellspacing="0" cellpadding="3" border="1" width="100%" bordercolordark="#CCCCCC" bordercolorlight="#CCCCCC" style="border-color:#000000;">
		<tr bgcolor="#e8e8e0" align="center">
			<td>房号</td>
			<td>床位</td>
			<td>姓名</td>
			<td>工号</td>
			<td>部门</td>
			<td>分部门</td>
			<td>性别</td>
			<td>入住日期</td>
			<td>籍贯</td>
			<td>居住证</td>
			<td>退宿日期</td>
			<td>入职日期</td>
			<td>离职日期</td>
		</tr>
		<% for(int i = 0;i <  totelList.getRowCount();i++){ 
		%>
			
		<tr>
			<td><%= totelList.getValue(i,"code") %></td>
			<td><%= totelList.getValue(i,"bednum") %></td>
			<td><%= totelList.getValue(i,"name") %></td>
			<td><%= totelList.getValue(i,"employnum") %></td>
			<td><%= totelList.getValue(i,"up_department") %></td>
			<td><%= totelList.getValue(i,"systemid") %></td>
			<td><%= totelList.getValue(i,"sex") %></td>
			<td><%= totelList.getValue(i,"livedate") %></td>
			<td><%= totelList.getValue(i,"nativeplace") %></td>
			<td><%= totelList.getValue(i,"cerno") %></td>
			<td><%= totelList.getValue(i,"leavedate") %></td>
			<td><%= totelList.getValue(i,"entrydate") %></td>
			<td><%= totelList.getValue(i,"Resignationdateq") %></td>
		</tr>
		
		<%
		} 
		%>
	</table>
</body>
</html>
